Upgrade Postgres

PGDay Paris
2023-03-23

Image by Gerd Altmann from Pixabay
logo EDB

Who am I

Image by Anemone123 from Pixabay

Upgrading Postgres

  • Postgres roadmap
  • Why upgrade?
  • What to do when
Image by Jan Vašek from Pixabay
logo EDB

Postgres Roadmap

  • Patches creation
  • Commit fests
  • Minor/Major
  • Feature freeze

Image by Valentin from Pixabay
logo EDB

Postgres Roadmap

Visualization fo the road map: from april n-a to April n: coding and code reviewing, in April year n: feature freeze, between May year n and September: Beta version, in September/October: Release Candidate, October year n: new major version logo EDB

Commit fests

  • 1 month
  • 5 times a year
  • Everyone is welcome!
Image by ktphotography from Pixabay

Minor versions

  • No new feature
  • bug fix
  • security fix
  • At least once per quarter
  • Read the release notes!
Image by Daniel Albany from Pixabay

Major versions



  • New features
  • Once a year
  • During fall

Feature freeze

  • Very strict
  • Everything after -> next version

Why upgrade?

  • To be secured
  • To keep up
  • To give back to the community
Image by Pexels from Pixabay

Security

  • That's important!
  • No code is bug-free
  • No code is secured
  • Flaws are published after minor releases

Softwares validated against a minor version

  • non-sense
  • creates security flaws in IT architecture
  • shows how little you know about Postgres

Postgres supported versions policy

  • 5 major versions
  • Favoring retrocompliance
  • But we still need to keep going
Image by fxquadro on Freepik

Keeping up

  • Unsupported versions are a risk
  • Avoiding big jumps of versions
  • Benefiting of performance enhancements
  • Benefiting of new features
Image by Shri ram from Pixabay

Giving back to the community

Image by NoName_13 from Pixabay

How to upgrade?

  • Tools
  • What is the best solution?

It depends!

Can you afford a service disruption?

  • Yes. How much?
  • No. Are you sure? even 5 minutes?

Release notes

  • Read them!
  • All of them!
  • That's right, all between your minor version to your minor target version!
Image by Jess Bailey from Pixabay

Tools

  • Exporting/importing
  • pg_upgrade
  • Logical Replication
Image by 4956365 from Pixabay

Exporting/importing

  • Service disruption (duration depends on the volume of data)
  • Removes bloat
  • Removes index corruption(if any)
Image by postcardtrip from Pixabay

Exporting/importing

  1. Install the new version of Postgres
  2. Create the new instance
  3. Take a physical backup
  4. Test your backup

Exporting/importing

  1. Stop the app
  2. Make sure you have all the WALs
  3. Export the non database objects
  4. Export your databases one by one
  5. Import the non database objects

Exporting/importing

  1. Import the databases one by one
  2. Stop the old instance
  3. Configure and start the app
  4. Take another backup

pg_upgrade

  • Service disruption
  • Link mode
  • Simple
Image by Joe from Pixabay

pg_upgrade

  1. Install the new version of Postgres
  2. Create the new instance
  3. Take a physical backup
  4. Test your backup
Image by Joe from Pixabay

pg_upgrade

  1. Use pg_upgrade
  2. Start the app
  3. Take another backup
Image by Joe from Pixabay

Logical Replication

  • Near-zero service disruption
  • More complex (Sequences)
Image by 1035352 from Pixabay

Logical Replication

  1. Install the new version of Postgres
  2. Create the new instance
  3. Setup the logical replication
  4. Stop the app
Image by 1035352 from Pixabay

Logical Replication

  1. Set the sequences
  2. Configure and start the app
  3. Remove the logical replication
  4. Take another backup
Image by 1035352 from Pixabay

One big problem: extensions

  • Compliance with Postgres versions
  • Migration path
  • Add other tool compliance into the mix

One solution: avoid big jumps and plan ahead!

Image by Tim Hill from Pixabay

Questions?

Image by Jon Kline from Pixabay